package com.java.main.util;

import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;
import org.springframework.util.CollectionUtils;

public class SqlAssembleUtils {

	/** sql中的in */
	private static final String IN = "IN";

	/** sql中的not in */
	private static final String NOT_IN = "NOT IN";

	/**
	 * 构建insert语句
	 *
	 * @param entity
	 *            实体映射对象
	 * @param tableName
	 *            表名称
	 * @return bound sql
	 */
	public static BoundSql buildInsertSql(Object entity, String tableName) {
		StringBuilder sql = new StringBuilder("INSERT INTO ");
		List<Object> params = new ArrayList<Object>();
		sql.append(tableName);
		sql.append("(");
		StringBuilder args = new StringBuilder();
		args.append("(");
		Method[] methods = entity.getClass().getDeclaredMethods();
		for (int i = 0; i < methods.length; i++) {
			String name = methods[i].getName();
			if (name.startsWith("get")) {
				name = name.substring(3);
				Object v = null;
				try {
					v = methods[i].invoke(entity, new Object[] {});
				} catch (Exception e) {
					e.printStackTrace();
				}
				if (v != null) {
					String columnName = StringUtils.getUnderlineName(name);
					sql.append(columnName);
					args.append("?");
					params.add(v);
					sql.append(",");
					args.append(",");
				}
			}
		}
		sql.deleteCharAt(sql.length() - 1);
		args.deleteCharAt(args.length() - 1);
		args.append(")");
		sql.append(")");
		sql.append(" VALUES ");
		sql.append(args);
		return new BoundSql(sql.toString(), params);
	}

	/**
	 * 构建insert语句
	 *
	 * @param entity
	 *            实体映射对象
	 * @param tableName
	 *            表名称
	 * @return bound sql
	 * @throws Exception
	 */
	public static BoundSql buildUpdateSql(Object entity, String tableName, String primaryName) throws Exception {
		List<AutoField> autoFields = new ArrayList<AutoField>();

		List<AutoField> entityAutoField = getEntityAutoField(entity, AutoField.UPDATE_FIELD);

		// 添加到后面，防止or等操作被覆盖
		autoFields.addAll(entityAutoField);

		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();

		sql.append("UPDATE ").append(tableName).append(" SET ");

		Object primaryValue = null;

		Iterator<AutoField> iterator = autoFields.iterator();
		while (iterator.hasNext()) {

			AutoField autoField = iterator.next();

			if (AutoField.UPDATE_FIELD != autoField.getType()) {
				continue;
			}

			String columnName = autoField.getName();

			// 如果是主键
			if (StringUtils.equalsIgnoreCase(primaryName, columnName)) {

				Object[] values = autoField.getValues();

				if (ArrayUtils.isEmpty(values) || StringUtils.isBlank(values[0].toString())) {
					throw new Exception("主键值不能设为空");
				}
				primaryValue = values[0];
			}

			sql.append(columnName).append(" ").append(autoField.getFieldOperator()).append(" ");
			if (ArrayUtils.isEmpty(autoField.getValues()) || autoField.getValues()[0] == null) {
				sql.append("NULL");
			} else {
				sql.append("?");
				params.add(autoField.getValues()[0]);
			}
			sql.append(",");

			// 移除掉操作过的元素
			iterator.remove();
		}

		sql.deleteCharAt(sql.length() - 1);
		sql.append(" WHERE ");

		if (primaryValue != null) {
			sql.append(primaryName).append(" = ?");
			params.add(primaryValue);
		} else {
			BoundSql boundSql = SqlAssembleUtils.builderWhereSql(autoFields);
			sql.append(boundSql.getSql());
			params.addAll(boundSql.getParams());
		}
		return new BoundSql(sql.toString(), params);
	}

	/**
	 * 构建根据id查询sql
	 *
	 * @param clazz
	 *            the clazz
	 * @param id
	 *            the id
	 * @param criteria
	 *            the criteria
	 * @param nameHandler
	 *            the name handler
	 * @return bound sql
	 */
	public static BoundSql buildByIdSql(Class<?> clazz, String tableName, Long id) {
		String columns = SqlAssembleUtils.buildColumnSql(clazz);
		String sql = "SELECT " + columns + " FROM " + tableName + " WHERE id = ?";
		List<Object> params = new ArrayList<Object>();
		params.add(id);
		return new BoundSql(sql, params);
	}

	/**
	 * 按设置的条件构建查询sql
	 *
	 * @param entity
	 *            the entity
	 * @param criteria
	 *            the criteria
	 * @param nameHandler
	 *            the name handler
	 * @return bound sql
	 */
	public static BoundSql buildQuerySql(Object entity, String tableName) {

		Class<?> entityClass = getEntityClass(entity);
		List<AutoField> autoFields = new ArrayList<AutoField>();
		List<AutoField> entityAutoField = getEntityAutoField(entity, AutoField.WHERE_FIELD);
		autoFields.addAll(entityAutoField);
		String columns = SqlAssembleUtils.buildColumnSql(entityClass);
		StringBuilder querySql = new StringBuilder("SELECT " + columns + " FROM ");
		querySql.append(tableName);

		List<Object> params = Collections.EMPTY_LIST;
		if (!CollectionUtils.isEmpty(autoFields)) {
			querySql.append(" WHERE ");

			BoundSql boundSql = SqlAssembleUtils.builderWhereSql(autoFields);
			params = boundSql.getParams();
			querySql.append(boundSql.getSql());
		}

		return new BoundSql(querySql.toString(), params);
	}

	public static BoundSql buildRangeQuerySql(Object entity, String tableName) {

		Class<?> entityClass = getEntityClass(entity);
		List<AutoField> autoFields = new ArrayList<AutoField>();
		List<AutoField> entityAutoField = getEntityAutoField(entity, AutoField.WHERE_FIELD);
		autoFields.addAll(entityAutoField);
		{
			AutoField autoField = new AutoField();
			autoField.setName("is_del");
			autoField.setSqlOperator("and");
			autoField.setFieldOperator("=");
			autoField.setValues(new Object[] { 0 });
			autoField.setType(AutoField.WHERE_FIELD);
			autoFields.add(autoField);
		}
		String columns = SqlAssembleUtils.buildColumnSql(entityClass);
		StringBuilder querySql = new StringBuilder("SELECT " + columns + " FROM ");
		querySql.append(tableName);

		List<Object> params = Collections.EMPTY_LIST;
		if (!CollectionUtils.isEmpty(autoFields)) {
			querySql.append(" WHERE ");

			BoundSql boundSql = SqlAssembleUtils.builderRangeSql(autoFields);
			params = boundSql.getParams();
			querySql.append(boundSql.getSql());
		}

		return new BoundSql(querySql.toString(), params);
	}

	/**
	 * 构建列表查询sql
	 *
	 * @param entity
	 *            the entity
	 * @param criteria
	 *            the criteria
	 * @param nameHandler
	 *            the name handler
	 * @return bound sql
	 */
	public static BoundSql buildListSql(Object entity, String tableName, String orderByKey) {

		BoundSql boundSql = SqlAssembleUtils.buildQuerySql(entity, tableName);
		boundSql.setOrderByKey(orderByKey);
		StringBuilder sb = new StringBuilder(" ORDER BY ");

		if (sb.length() < 11) {
			orderByKey = orderByKey.toLowerCase();
			if (orderByKey.indexOf("desc") == -1 && orderByKey.indexOf("asc") == -1) {
				sb.append(boundSql.getOrderByKey()).append(" desc ");
			} else {
				sb.append(boundSql.getOrderByKey());
			}
		}
		boundSql.setSql(boundSql.getSql() + sb.toString());
		return boundSql;
	}

	public static BoundSql buildRangeSql(Object entity, String tableName, String orderByKey) {

		BoundSql boundSql = SqlAssembleUtils.buildRangeQuerySql(entity, tableName);
		boundSql.setOrderByKey(orderByKey);
		StringBuilder sb = new StringBuilder(" ORDER BY ");

		if (sb.length() < 11) {
			orderByKey = orderByKey.toLowerCase();
			if (orderByKey.indexOf("desc") == -1 && orderByKey.indexOf("asc") == -1) {
				sb.append(boundSql.getOrderByKey()).append(" desc ");
			} else {
				sb.append(boundSql.getOrderByKey());
			}
		}
		boundSql.setSql(boundSql.getSql() + sb.toString());
		return boundSql;
	}

	/**
	 * 构建记录数查询sql
	 *
	 * @param entity
	 *            the entity
	 * @param criteria
	 *            the criteria
	 * @param nameHandler
	 *            the name handler
	 * @return bound sql
	 */
	public static BoundSql buildCountSql(Object entity, String tableName) {

		Class<?> entityClass = getEntityClass(entity);
		List<AutoField> autoFields = new ArrayList<AutoField>();
		List<AutoField> entityAutoField = getEntityAutoField(entity, AutoField.WHERE_FIELD);
		autoFields.addAll(entityAutoField);
		StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM ");
		countSql.append(tableName);
		List<Object> params = Collections.EMPTY_LIST;
		if (!CollectionUtils.isEmpty(autoFields)) {
			countSql.append(" WHERE ");
			BoundSql boundSql = builderWhereSql(autoFields);
			countSql.append(boundSql.getSql());
			params = boundSql.getParams();
		}

		return new BoundSql(countSql.toString(), params);
	}

	/**
	 * 构建where条件sql
	 *
	 * @param autoFields
	 *            the auto fields
	 * @param nameHandler
	 *            the name handler
	 * @return bound sql
	 */
	public static BoundSql builderWhereSql(List<AutoField> autoFields) {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		Iterator<AutoField> iterator = autoFields.iterator();
		while (iterator.hasNext()) {
			AutoField autoField = iterator.next();
			if (AutoField.WHERE_FIELD != autoField.getType()) {
				continue;
			}
			// 操作过，移除
			iterator.remove();
			String columnName = autoField.getName();
			Object[] values = autoField.getValues();

			if (sql.length() > 0) {
				sql.append(" ").append(autoField.getSqlOperator()).append(" ");
			}

			if (StringUtils.equalsIgnoreCase(IN, StringUtils.trim(autoField.getFieldOperator()))
					|| StringUtils.equalsIgnoreCase(NOT_IN, StringUtils.trim(autoField.getFieldOperator()))) {

				// in，not in的情况
				sql.append(columnName).append(" ").append(autoField.getFieldOperator()).append(" ");
				sql.append("(");
				for (int j = 0; j < values.length; j++) {
					sql.append(" ?");
					params.add(values[j]);
					if (j != values.length - 1) {
						sql.append(",");
					}
				}
				sql.append(")");
			} else if (values == null) {
				// null 值
				sql.append(columnName).append(" ").append(autoField.getFieldOperator()).append(" NULL");
			} else if (values.length == 1) {
				// 一个值 =
				sql.append(columnName).append(" ").append(autoField.getFieldOperator()).append(" '" + values[0] + "'");
				params.add(values[0]);
			} else {
				// 多个值，or的情况
				sql.append("(");
				for (int j = 0; j < values.length; j++) {
					sql.append(columnName).append(" ").append(autoField.getFieldOperator())
							.append(" '" + values[0] + "'");
					params.add(values[j]);
					if (j != values.length - 1) {
						sql.append(" OR ");
					}
				}
				sql.append(")");
			}
		}
		return new BoundSql(sql.toString(), params);
	}

	private static BoundSql builderRangeSql(List<AutoField> autoFields) {
		Calendar now = Calendar.getInstance();
		int year = now.get(Calendar.YEAR);
		ArrayList<String> rangeY = new ArrayList<String>();

		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		Iterator<AutoField> iterator = autoFields.iterator();

		for (int j = 0; j < 5; j++) {
			int large = 100;
			int small = 0;
			if (j == 0) {
				rangeY.add(j,
						"SUBSTR(id_card_number, 7, 4) BETWEEN " + (year - large) + " and " + (year - small) + " ");
			} else {
				large = 25 + (j - 1) * 5;
				small = 20 + (j - 1) * 5;
				rangeY.add(j,
						"SUBSTR(id_card_number, 7, 4) BETWEEN " + (year - large) + " and " + (year - small) + " ");
			}
		}

		while (iterator.hasNext()) {
			AutoField autoField = iterator.next();
			if (AutoField.WHERE_FIELD != autoField.getType()) {
				continue;
			}
			// 操作过，移除
			iterator.remove();
			if (sql.length() > 0) {
				sql.append(" ").append(autoField.getSqlOperator()).append(" ");
			}
			String columnName = autoField.getName();
			Object[] values = autoField.getValues();

			if (StringUtils.equalsIgnoreCase(IN, StringUtils.trim(autoField.getFieldOperator()))
					|| StringUtils.equalsIgnoreCase(NOT_IN, StringUtils.trim(autoField.getFieldOperator()))) {

				// in，not in的情况
				sql.append(columnName).append(" ").append(autoField.getFieldOperator()).append(" ");
				sql.append("(");
				for (int j = 0; j < values.length; j++) {
					sql.append(" ?");
					params.add(values[j]);
					if (j != values.length - 1) {
						sql.append(",");
					}
				}
				sql.append(")");
			} else if (values == null) {
				// null 值
				sql.append(columnName).append(" ").append(autoField.getFieldOperator()).append(" NULL");
			} else if (values.length == 1 && columnName != "is_del") {
				// 一个值 =
				String a = values[0].toString();
				Integer i = Integer.parseInt(a);
				sql.append(rangeY.get(i));
			} else if (values.length == 1) {
				// 一个值 =
				sql.append(columnName).append(" ").append(autoField.getFieldOperator()).append(" ?");
				params.add(values[0]);
			} else {
				// 多个值，or的情况
				sql.append("(");
				for (int j = 0; j < values.length; j++) {
					sql.append(columnName).append(" ").append(autoField.getFieldOperator()).append(" ?");
					params.add(values[j]);
					if (j != values.length - 1) {
						sql.append(" OR ");
					}
				}
				sql.append(")");
			}
		}
		return new BoundSql(sql.toString(), params);
	}

	public static List<AutoField> mapToAutoFieldList(Map<String, Object> param) {
		List<AutoField> fieldList = new ArrayList<AutoField>();
		for (Map.Entry<String, Object> entry : param.entrySet()) {
			String key = entry.getKey();
			Object value = entry.getValue();
			if (value != null) {
				key = StringUtils.getUnderlineName(key);
				AutoField autoField = new AutoField();
				autoField = new AutoField();
				if (value instanceof Date) {
					autoField.setName("DATE_FORMAT(" + key + ",'%Y-%m-%d')");
					value = StringUtils.DateToStringyyyyMMdd((Date) value);
				} else {
					autoField.setName(key);
				}
				autoField.setSqlOperator("and");
				autoField.setFieldOperator("=");
				autoField.setValues(new Object[] { value });
				autoField.setType(AutoField.WHERE_FIELD);
				fieldList.add(autoField);
			}
		}
		return fieldList;
	}

	/**
	 * 获取所有的操作属性，entity非null字段将被转换到列表
	 *
	 * @param entity
	 *            the entity
	 * @param operateType
	 *            the operate type
	 * @return all auto field
	 */
	public static List<AutoField> getEntityAutoField(Object entity, int operateType) {

		// 汇总的所有操作属性
		List<AutoField> autoFieldList = new ArrayList<AutoField>();

		if (entity == null) {
			return autoFieldList;
		}
		AutoField autoField;
		Method[] methods = entity.getClass().getDeclaredMethods();
		for (int i = 0; i < methods.length; i++) {
			Method method = methods[i];
			String name = method.getName();
			if (name.startsWith("get")) {

				String fieldName = name.substring(3);
				fieldName = StringUtils.getUnderlineName(fieldName);

				if ("size".equals(fieldName) || "results".equals(fieldName) || "page".equals(fieldName)
						|| "sort_field".equals(fieldName) || "sort_order".equals(fieldName)
						|| "kbm_content".equals(fieldName)) {// 如果有这4个字段不做处理
					continue;
				}

				// null值，忽略 (单独指定的可以指定为null)
				Object value = null;
				try {
					value = method.invoke(entity, new Object[] {});
				} catch (Exception e) {
					System.out.println("获取值出错，表：" + entity.toString() + "；字段" + fieldName);
					e.printStackTrace();
				}
				if (value == null) {
					continue;
				}
				if (value instanceof String && StringUtils.isEmpty(value.toString())) {
					continue;
				}
				autoField = new AutoField();
				if (value instanceof Date) {
					autoField.setName("DATE_FORMAT(" + fieldName + ",'%Y-%m-%d')");
					value = StringUtils.DateToStringyyyyMMdd((Date) value);
				} else {
					autoField.setName(fieldName);
				}
				autoField.setSqlOperator("and");
				autoField.setFieldOperator("=");
				autoField.setValues(new Object[] { value });
				autoField.setType(operateType);

				autoFieldList.add(autoField);
			}
		}
		return autoFieldList;
	}

	/**
	 * 构建查询的列sql
	 *
	 * @param clazz
	 *            the clazz
	 * @return string string
	 */
	public static String buildColumnSql(Class<?> clazz) {

		StringBuilder columns = new StringBuilder();

		// 获取属性信息
		Method[] methods = clazz.getDeclaredMethods();
		for (int i = 0; i < methods.length; i++) {
			String name = methods[i].getName();
			if (name.startsWith("get")) {
				name = name.substring(3);
				name = StringUtils.getUnderlineName(name);
				columns.append(name);
				columns.append(",");
			}
		}
		columns.deleteCharAt(columns.length() - 1);
		return columns.toString();
	}

	/**
	 * 构建根据主键删除sql
	 *
	 * @param clazz
	 * @param id
	 * @param nameHandler
	 * @return
	 */
	public static BoundSql buildDeleteSql(String tableName, String primaryName, Long id) {
		List<Object> params = new ArrayList<Object>();
		params.add(id);
		String sql = "DELETE FROM " + tableName + " WHERE " + primaryName + " = ?";
		return new BoundSql(sql, params);
	}

	/**
	 * 构建删除sql
	 *
	 * @param entity
	 *            the entity
	 * @param criteria
	 *            the criteria
	 * @param nameHandler
	 *            the name handler
	 * @return bound sql
	 */
	public static BoundSql buildDeleteSql(Object entity, String tableName) {
		Class<?> entityClass = getEntityClass(entity);
		List<AutoField> autoFields = new ArrayList<AutoField>();
		List<AutoField> entityAutoField = getEntityAutoField(entity, AutoField.WHERE_FIELD);
		autoFields.addAll(entityAutoField);
		// StringBuilder sql = new StringBuilder("DELETE FROM " + tableName + "
		// WHERE ");
		StringBuilder sql = new StringBuilder("update " + tableName + " set is_del=1 WHERE ");
		BoundSql boundSql = SqlAssembleUtils.builderWhereSql(autoFields);
		boundSql.setSql(sql.append(boundSql.getSql()).toString());

		return boundSql;
	}

	public static Class<?> getEntityClass(Object entity) {
		return entity.getClass();
	}

	public static void mian(String[] arge) {
		List<AutoField> autoFields = new ArrayList<AutoField>();
		AutoField autoField = new AutoField();
		autoField.setName("key");
		autoField.setSqlOperator("and");
		autoField.setFieldOperator("=");
		autoField.setValues(new Object[] { "value" });
		autoField.setType(AutoField.WHERE_FIELD);
		autoFields.add(autoField);
		BoundSql sql = builderWhereSql(autoFields);
		System.out.println(sql.getSql());
	}
}
